package com.apes.fn.server.productRequire;

import com.apes.framework.jpa.repository.CustomRepository;
import org.springframework.data.jpa.repository.Query;
import org.springframework.data.repository.query.Param;

import java.util.List;
import java.util.Map;

public interface ProductRequireExpandRepository extends CustomRepository<ProductRequireExpand, String> {

    @Query(value = "" +
            "SELECT DISTRIBUTION_ID, ORIGIN, ORIGIN_ITEM_ID, ORIGIN_BUSINESS_TYPE, PRODUCT_UOM_ID, PRODUCT_ID, REQUIRE_QTY, " +
            "       (ASSIGN_QTY - SHIP_QTY - CANCEL_QTY) CANCEL_QTY, (ASSIGN_QTY - SHIP_QTY) SHIP_QTY, " +
            "       (ASSIGN_QTY - ARRIVE_QTY) ARRIVE_QTY " +
            "  FROM STOCK_PRODUCT_REQUIRE_EXPAND " +
            " WHERE ASSIGN_QTY - CANCEL_QTY > 0 " +
            "   AND DISTRIBUTION_ID = :distributionId " +
            "   AND PRODUCT_ID = :productId " +
            " ORDER BY ID ", nativeQuery = true)
    List<Map> qryCanCancelData(@Param("distributionId") String distributionId, @Param("productId") String productId);

    @Query(value = "SELECT * FROM STOCK_PRODUCT_REQUIRE_EXPAND WHERE DISTRIBUTION_ID = :distributionId ", nativeQuery = true)
    List<ProductRequireExpand> qryRequireExpand(@Param("distributionId") String distributionId);

    @Query(value = "SELECT * FROM STOCK_PRODUCT_REQUIRE_EXPAND WHERE DISTRIBUTION_ID = ?1 AND ORIGIN_ITEM_ID = ?2 ", nativeQuery = true)
    Map accordDistributIdAndOriginItemId(String distributionId, String originItemId);

    @Query(value = "SELECT * FROM STOCK_PRODUCT_REQUIRE_EXPAND WHERE DISTRIBUTION_ID = ?1 AND PRODUCT_ID = ?2 ", nativeQuery = true)
    List<ProductRequireExpand> accordDistributIdAndProId(String distributionId, String productId);

    @Query(value = "" +
            " SELECT ORIGIN, DISTRIBUTION_ID FROM STOCK_PRODUCT_REQUIRE_EXPAND " +
            "  WHERE ORIGIN IN ?1 " +
            "    AND TO_DATE(TO_CHAR(CREATE_DATE, 'YYYY/MM/DD HH24:MI:SS'), 'YYYY/MM/DD HH24:MI:SS') >= TO_DATE(?2, 'YYYY/MM/DD HH24:MI:SS') " +
            "    AND TO_DATE(TO_CHAR(CREATE_DATE, 'YYYY/MM/DD HH24:MI:SS'), 'YYYY/MM/DD HH24:MI:SS') <= TO_DATE(?3, 'YYYY/MM/DD HH24:MI:SS') ", nativeQuery = true)
    List<Map> replemeDumpGetId(List origin, String startTime, String endTime);


    @Query(value = "SELECT * FROM STOCK_PRODUCT_REQUIRE_EXPAND WHERE DISTRIBUTION_ID = ?1 AND  origin_item_id = ?2 AND ASSIGN_QTY - SHIP_QTY > 0 ", nativeQuery = true)
    ProductRequireExpand accordDistributionIdAndOriginItemIdFind(String distributionId, String originItemId);

}
